package com.fau.wmbj.server.utils;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.map.MapUtil;
import org.apache.commons.lang3.StringUtils;

import java.util.*;

/**
 * 工具类
 *
 * @author jiawei
 * @version 1.0
 * @datetime 2024/4/22 上午9:46
 */
public class LuckySheet {

    public static List<Map<String, Object>> filterData2(List<Map<String, Object>> bodys, Integer rowNum, List<String> filter) {
        for (int j = 0; j < bodys.size(); j++) {
            Map<String, Object> sheet = bodys.get(j);
            // 需要删除的列序号
            List<Integer> cols = CollectionUtil.newArrayList();
            List<Map<String, Object>> celldata = (List<Map<String, Object>>) sheet.get("celldata");
            for (Map<String, Object> cell : celldata) {
                Integer r = (Integer) cell.get("r");
                Integer c = (Integer) cell.get("c");
                if (Objects.equals(rowNum, r)) {
                    Map<String, Object> hv = (Map<String, Object>) cell.get("v");
                    Object v = hv.get("v");
                    if (v != null) {
                        if (!CollectionUtil.contains(filter, v)) {
                            cols.add(c);
                        }
                    }
                } else if (r > rowNum) {
                    break;
                }
            }
            if (CollectionUtil.isNotEmpty(cols)) {
                Map<String, Object> config = (Map<String, Object>) sheet.get("config");
                Map<String, Object> colhidden = new HashMap<>();

                for (int i = 0; i < cols.size(); i++) {
                    Integer i1 = cols.get(i);
                    colhidden.put(i1.toString(), 0);
                }
                config.put("colhidden", colhidden);
                sheet.put("config", config);
            }

        }
        return bodys;
    }

    public static List<Map<String, Object>> filterData(List<Map<String, Object>> body, Integer rowNum, List<String> filter) {
        // 需要删除的列序号
        List<Integer> cols = CollectionUtil.newArrayList();
        // 遍历所有sheet
        Map<String, Object> sheet = body.get(0);
        List<Map<String, Object>> celldata = (List<Map<String, Object>>) sheet.get("celldata");
        for (Map<String, Object> cell : celldata) {
            Integer r = (Integer) cell.get("r");
            Integer c = (Integer) cell.get("c");
            if (Objects.equals(rowNum, r)) {
                Map<String, Object> hv = (Map<String, Object>) cell.get("v");
                String v = (String) hv.get("v");
                if (StringUtils.isNotBlank(v)) {
                    if (!CollectionUtil.contains(filter, v)) {
                        cols.add(c);
                    }
                }
            } else if (r > rowNum) {
                break;
            }
        }

        // for (Map<String, Object> sheet : body) {
        // List<List<Map<String, Object>>> rowList = (List<List<Map<String, Object>>>) sheet.get("data");
        // for (int i = 0; i < rowList.size(); i++) {
        //     List<Map<String, Object>> colList = rowList.get(i);
        //     if (i == rowNum) {
        //         for (int j = 0; j < colList.size(); j++) {
        //             Map<String, Object> colData = colList.get(j);
        //             Map<String, Object> mc = (Map<String, Object>) colData.get("mc");
        //             String m = (String) colData.get("m");
        //             Map<String, Object> ct = (Map<String, Object>) colData.get("ct");
        //             String v = (String) colData.get("v");
        //             if (StringUtils.isNotBlank(v) && !CollectionUtil.contains(filter, v)) {
        //                 cols.add(j);
        //             }
        //         }
        //     }
        // }
        // }

        cols.sort(Comparator.comparingInt(o -> o));
        Map<String, Object> newSheet = body.get(0);
        //删除列并返回删除后的数据
        deleteCol(newSheet, cols);
        List<Map<String, Object>> objects = new ArrayList<>();
        objects.add(newSheet);
        return objects;
    }

    private static Map<String, Object> deleteCol(Map<String, Object> sheet, List<Integer> allcols) {
        List<List<Map<String, Object>>> data = (List<List<Map<String, Object>>>) sheet.get("data");
        List<Map<String, Object>> celldata = (List<Map<String, Object>>) sheet.get("celldata");
        Map<String, Object> config = (Map<String, Object>) sheet.get("config");
        Map<String, Object> merge = (Map<String, Object>) config.get("merge");
        Map<String, Object> merge_new = new HashMap<>();
        List<List<Map<String, Object>>> newdata = new ArrayList<>();
        List<Map<String, Object>> newcelldata = new ArrayList<>();
        Map<String, Object> merge_new2 = new HashMap<>();
        //合并单元格
        for (Map.Entry<String, Object> entry : merge.entrySet()) {
            Map<String, Object> value = (Map<String, Object>) entry.getValue();
            Integer r = (Integer) value.get("r");
            Integer c = (Integer) value.get("c");
            Integer rs = (Integer) value.get("rs");
            Integer cs = (Integer) value.get("cs");
            Map<String, Object> v = new HashMap<>();
            String k = r + "_" + c;
            int flag = 0;
            if (allcols.contains(c) && cs == 1) {
                continue;
            }
            for (int i = c; i <= c + cs - 1; i++) {
                if (allcols.contains(i)) {
                    flag++;
                }
            }
            if (flag == cs) {
                continue;
            }
            int lessthan_c = 0;
            int equal_c = 0;
            int between_c_cs = 0;
            int greaterthan_cs = 0;
            for (int i = 0; i < allcols.size(); i++) {
                if (allcols.get(i) < c) {
                    lessthan_c = lessthan_c + 1;
                }
                if (allcols.get(i) == c) {
                    equal_c = equal_c + 1;
                }
                if (allcols.get(i) > c && allcols.get(i) <= (c + cs - 1)) {
                    between_c_cs = between_c_cs + 1;
                }
                if (allcols.get(i) > (c + cs)) {
                    greaterthan_cs = greaterthan_cs + 1;
                }
            }
            if (lessthan_c > 0) {
                c = c - lessthan_c;
            }
            if (between_c_cs > 0) {
                cs = cs - between_c_cs - equal_c;
            }
            if (cs <= 0) {
                cs = 1;
            }
            Integer finalC = c;
            Integer finalCs = cs;
            merge_new.put(r + "_" + c, new HashMap<String, Object>() {{
                put("r", r);
                put("c", finalC);
                put("rs", rs);
                put("cs", finalCs);
            }});
            v.put("r", r);
            v.put("c", c);
            v.put("rs", rs);
            v.put("cs", cs);
            merge_new2.put(k, v);
        }


        //删除单元格
        for (int i = 0; i < data.size(); i++) {
            List<Map<String, Object>> cols = data.get(i);
            List<Map<String, Object>> newcols = new ArrayList<>();
            for (int j = 0; j < cols.size(); j++) {
                if (CollectionUtil.contains(allcols, j)) {
                    continue;
                }
                newcols.add(cols.get(j));
            }
            newdata.add(newcols);
        }

        for (int i = 0; i < celldata.size(); i++) {
            Map<String, Object> cell = celldata.get(i);
            Map<String, Object> cellv = (Map<String, Object>) cell.get("v");
            Map<String, Object> mc = (Map<String, Object>) cellv.get("mc");
            if (MapUtil.isNotEmpty(mc)) {
                Integer r = (Integer) mc.get("r");
                Integer c = (Integer) mc.get("c");
                Map<String, Object> v = (Map<String, Object>) merge_new2.get(r + "_" + c);
                if (MapUtil.isNotEmpty(v)) {
                    // cellv.put("mc", v);
                }
            }
            Integer c = (Integer) cell.get("c");
            if (allcols.contains(c)) {
                continue;
            }
            int flag = 0;
            for (int j = 0; j < allcols.size(); j++) {
                if (c > allcols.get(j)) {
                    flag++;
                }
            }
            c = c - flag;
            cell.put("c", c);
            newcelldata.add(cell);
        }
        config.put("merge", merge_new);
        sheet.put("data", new ArrayList<>());
        sheet.put("celldata", newcelldata);
        sheet.put("config", config);
        return sheet;
    }

    public static Map<String, Object> deleteRow(Map<String, Object> sheet, Integer start, Integer end) {
        List<Map<String, Object>> celldata = (List<Map<String, Object>>) sheet.get("celldata");
        Map<String, Object> config = (Map<String, Object>) sheet.get("config");
        Map<String, Object> merge = (Map<String, Object>) config.get("merge");
        Map<String, Object> new_merge = new HashMap<>();
        Map<String, Object> merge_new2 = new HashMap<>();
        List<Map<String, Object>> new_celldata = new ArrayList<>();
        for (Map.Entry<String, Object> entry : merge.entrySet()) {
            Map<String, Object> value = (Map<String, Object>) entry.getValue();
            Integer r = (Integer) value.get("r");
            Integer c = (Integer) value.get("c");
            Integer rs = (Integer) value.get("rs");
            Integer cs = (Integer) value.get("cs");
            Map<String, Object> v = new HashMap<>();
            String k = r + "_" + c;
            if (r > end) {
                r = r - 1;
            }
            if (r > start && (r + rs - 1) > end) {
                rs = (end - start) - (r - start);
                r = start;
            }
            if (start >= r && end <= (r + rs - 1)) {
                continue;
            }
            if (start >= r && start <= (r + rs - 1) && end > (r + rs - 1)) {
                rs = r + rs - 1 - start;
            }
            if (r >= start && (r + rs - 1) <= end) {
                continue;
            }
            Integer finalR = r;
            Integer finalRs = rs;
            new_merge.put(r + "_" + value.get("c"), new HashMap<String, Object>() {{
                put("r", finalR);
                put("c", value.get("c"));
                put("rs", finalRs);
                put("cs", value.get("cs"));
            }});
            v.put("r", r);
            v.put("c", c);
            v.put("rs", rs);
            v.put("cs", cs);
            merge_new2.put(k, v);
        }

        for (int i = 0; i < celldata.size(); i++) {
            Map<String, Object> cell = celldata.get(i);
            Map<String, Object> cellv = (Map<String, Object>) cell.get("v");
            Map<String, Object> mc = (Map<String, Object>) cellv.get("mc");
            if (MapUtil.isNotEmpty(mc)) {
                Integer r = (Integer) mc.get("r");
                Integer c = (Integer) mc.get("c");
                Map<String, Object> v = (Map<String, Object>) merge_new2.get(r + "_" + c);
                if (MapUtil.isNotEmpty(v)) {
                    cellv.put("mc", v);
                }
            }
            Integer r = (Integer) cell.get("r");
            if (r >= start && r <= end) {
                continue;
            } else {
                int flag = 0;
                for (int j = start; j <= end; j++) {
                    if (r > j) {
                        flag++;
                    }
                }
                r = r - flag;
                cell.put("r", r);
                new_celldata.add(cell);
            }

        }
        config.put("merge", new_merge);
        sheet.put("data", new ArrayList<>());
        sheet.put("celldata", new_celldata);
        sheet.put("config", config);
        return sheet;

    }

    //删除列
    private static Map<String, Object> deleteTableCols3(Map<String, Object> sheet, List<Integer> cols, String type) {
        List<List<Map<String, Object>>> data = (List<List<Map<String, Object>>>) sheet.get("data");
        Map<String, Object> config = (Map<String, Object>) sheet.get("config");
        Map<String, Object> merge = (Map<String, Object>) config.get("merge");
        Map<String, Object> calcChain = (Map<String, Object>) config.get("calcChain");
        Map<String, Object> hyperlink = (Map<String, Object>) config.get("hyperlink");
        Map<String, Object> filter_select = (Map<String, Object>) config.get("filter_select");
        Map<String, Object> filter = (Map<String, Object>) sheet.get("filter");

        Map<String, Object> merge_new = new HashMap<>();
        List<Map<String, Object>> newCalcChain = new ArrayList<>();
        Map<String, Object> newFilterObj = new HashMap<>();
        Map<String, Object> newDataVerification = new HashMap<>();
        Map<String, Object> newHyperlink = new HashMap<>();

        Integer st = cols.get(0);
        Integer ed = cols.get(cols.size() - 1);
        Integer slen = ed - st + 1;
        //合并单元格配置变动
        if (MapUtil.isEmpty(merge)) {
            merge = new HashMap<>();
        }
        for (Map.Entry<String, Object> entry : merge.entrySet()) {
            String key = entry.getKey();
            Map<String, Object> value = (Map<String, Object>) entry.getValue();
            Integer r = (Integer) value.get("r");
            Integer c = (Integer) value.get("c");
            Integer rs = (Integer) value.get("rs");
            Integer cs = (Integer) value.get("cs");
            if (c < st) {
                if (c + cs - 1 < st) {
                    merge_new.put(r + "_" + c, new HashMap<String, Object>() {
                        {
                            put("r", r);
                            put("c", c);
                            put("rs", rs);
                            put("cs", cs);
                        }
                    });
                } else if (c + cs - 1 >= st && c + cs - 1 < ed) {
                    merge_new.put(r + "_" + c, new HashMap<String, Object>() {
                        {
                            put("r", r);
                            put("c", c);
                            put("rs", rs);
                            put("cs", st - c);
                        }
                    });
                } else if (c + cs - 1 >= ed) {
                    merge_new.put(r + "_" + c, new HashMap<String, Object>() {
                        {
                            put("r", r);
                            put("c", c);
                            put("rs", rs);
                            put("cs", cs - slen);
                        }
                    });
                }
            } else if (c >= st && c <= ed) {
                if (c + cs - 1 > ed) {
                    merge_new.put(r + "_" + c, new HashMap<String, Object>() {
                        {
                            put("r", r);
                            put("c", st);
                            put("rs", rs);
                            put("cs", c + cs - 1 - ed);
                        }
                    });
                }
            } else if (c > ed) {
                merge_new.put(r + "_" + c, new HashMap<String, Object>() {
                    {
                        put("r", r);
                        put("c", c - slen);
                        put("rs", rs);
                        put("cs", cs);
                    }
                });
            }
        }
        // config.put("merge", merge_new);

        //公式变动
        if (calcChain != null && !calcChain.isEmpty()) {
            for (int i = 0; i < calcChain.size(); i++) {
                Map<String, Object> calc = (Map<String, Object>) calcChain.get(i);
                Integer calc_r = (Integer) calc.get("r");
                Integer calc_c = (Integer) calc.get("c");
                Integer calc_i = (Integer) calc.get("index");
                String calc_funcStr = getCellFormula(calc_r, calc_c, calc_i, data);
                if (StringUtils.equals(type, "row")) {
                    if (calc_r < st || calc_r > ed) {
                        String functionStr = "=" + Formula.functionStrChange(calc_funcStr, "del", "row", null, st, slen);
                        Map<String, Object> obj1 = data.get(calc_r).get(calc_c);
                        if (MapUtil.isNotEmpty(obj1) && StringUtils.equals((String) obj1.get("f"), calc_funcStr)) {
                            obj1.put("f", functionStr);
                        }
                        if (calc_r > ed) {
                            calc.put("r", calc_r - slen);
                        }
                        newCalcChain.add(calc);
                    }
                } else if (StringUtils.equals(type, "col")) {
                    if (calc_c < st || calc_c > ed) {
                        String functionStr = "=" + Formula.functionStrChange(calc_funcStr, "del", "col", null, st, slen);
                        Map<String, Object> obj1 = data.get(calc_r).get(calc_c);
                        if (MapUtil.isNotEmpty(obj1) && StringUtils.equals((String) obj1.get("f"), calc_funcStr)) {
                            obj1.put("f", functionStr);
                        }
                        if (calc_c > ed) {
                            calc.put("c", calc_c - slen);
                        }
                        newCalcChain.add(calc);
                    }
                }
            }
        }
        //筛选配置变动
        if (MapUtil.isNotEmpty(filter_select)) {
            newFilterObj.put("filter_select", new HashMap<>());
            newFilterObj.put("filter_select_save", new HashMap<>());
            List<Integer> rows = (List<Integer>) filter_select.get("row");
            List<Integer> columns = (List<Integer>) filter_select.get("column");
            Integer f_r1 = rows.get(0);
            Integer f_r2 = rows.get(1);
            Integer f_c1 = columns.get(0);
            Integer f_c2 = columns.get(1);
            if (StringUtils.equals(type, "row")) {
                if (f_r1 > ed) {
                    f_r1 -= slen;
                    f_r2 -= slen;
                    Integer finalF_r = f_r1;
                    Integer finalF_r1 = f_r2;
                    Integer finalF_c8 = f_c1;
                    Integer finalF_c9 = f_c2;
                    newFilterObj.put("filter_select", new HashMap<String, Object>() {
                        {
                            put("row", new ArrayList<Integer>() {
                                {
                                    add(finalF_r);
                                    add(finalF_r1);
                                }
                            });
                            put("column", new ArrayList<Integer>() {
                                {
                                    add(finalF_c8);
                                    add(finalF_c9);
                                }
                            });
                        }
                    });
                } else if (f_r1 < st) {
                    if (f_r2 < st) {
                    } else if (f_r2 <= ed) {
                        f_r2 = st - 1;
                    } else {
                        f_r2 -= slen;
                    }
                    Integer finalF_r2 = f_r1;
                    Integer finalF_r3 = f_r2;
                    Integer finalF_c6 = f_c1;
                    Integer finalF_c7 = f_c2;
                    newFilterObj.put("filter_select", new HashMap<String, Object>() {{
                        put("row", new ArrayList<Integer>() {{
                            add(finalF_r2);
                            add(finalF_r3);
                        }});
                        put("column", new ArrayList<Integer>() {{
                            add(finalF_c6);
                            add(finalF_c7);
                        }});
                    }});
                }
                Map<String, Object> filterSelect = (Map<String, Object>) newFilterObj.get("filter_select");
                if (MapUtil.isNotEmpty(filterSelect) && MapUtil.isNotEmpty(filter)) {
                    Map<String, Object> newFilterObjFilters = new HashMap<>();
                    for (Map.Entry<String, Object> entrya : filter.entrySet()) {
                        Map<String, Object> f = (Map<String, Object>) entrya.getValue();
                        Map<String, Object> f_rowhidden = (Map<String, Object>) f.get("rowhidden");
                        Map<String, Object> f_rowhidden_new = new HashMap<>();
                        //遍历map
                        for (Map.Entry<String, Object> entry : f_rowhidden.entrySet()) {
                            String key = entry.getKey();
                            Integer value = (Integer) entry.getValue();
                            if (Integer.parseInt(key) < st) {
                                f_rowhidden_new.put(key, 0);
                            } else if (Integer.parseInt(key) > ed) {
                                f_rowhidden_new.put(String.valueOf(Integer.parseInt(key) - slen), 0);
                            }
                        }
                        if (MapUtil.isNotEmpty(f_rowhidden_new)) {
                            Map<String, Object> newFilter = (Map<String, Object>) newFilterObj.get("filter");
                            if (CollectionUtil.isEmpty(newFilter)) {
                                newFilter = new HashMap<>();
                            }
                            newFilter.putAll(f);
                            newFilter.put("rowhidden", f_rowhidden_new);
                            newFilter.put("str", f_r1);
                            newFilter.put("edr", f_r2);
                            newFilterObjFilters.put(entrya.getKey(), newFilter);
                        }
                    }
                    newFilterObj.put("filter", newFilterObjFilters);
                }
            } else if (StringUtils.equals(type, "col")) {
                if (f_c1 > ed) {
                    f_c1 -= slen;
                    f_c2 -= slen;
                    Integer finalF_r4 = f_r2;
                    Integer finalF_r5 = f_r1;
                    Integer finalF_c = f_c1;
                    Integer finalF_c1 = f_c2;
                    newFilterObj.put("filter_select", new HashMap<String, Object>() {
                        {
                            put("row", new ArrayList<Integer>() {
                                {
                                    add(finalF_r5);
                                    add(finalF_r4);
                                }
                            });
                            put("column", new ArrayList<Integer>() {
                                {
                                    add(finalF_c);
                                    add(finalF_c1);
                                }
                            });
                        }
                    });
                } else if (f_c1 < st) {
                    if (f_c2 < st) {
                    } else if (f_c2 <= ed) {
                        f_c2 = st - 1;
                    } else {
                        f_c2 -= slen;
                    }
                    Integer finalF_r6 = f_r2;
                    Integer finalF_r7 = f_r1;
                    Integer finalF_c2 = f_c1;
                    Integer finalF_c3 = f_c2;
                    newFilterObj.put("filter_select", new HashMap<String, Object>() {
                        {
                            put("row", new ArrayList<Integer>() {
                                {
                                    add(finalF_r7);
                                    add(finalF_r6);
                                }
                            });
                            put("column", new ArrayList<Integer>() {
                                {
                                    add(finalF_c2);
                                    add(finalF_c3);
                                }
                            });
                        }
                    });
                } else {
                    if (f_c2 > ed) {
                        f_c1 = st;
                        f_c2 -= slen;
                        Integer finalF_r8 = f_r1;
                        Integer finalF_r9 = f_r2;
                        Integer finalF_c4 = f_c1;
                        Integer finalF_c5 = f_c2;
                        newFilterObj.put("filter_select", new HashMap<String, Object>() {
                            {
                                put("row", new ArrayList<Integer>() {
                                    {
                                        add(finalF_r8);
                                        add(finalF_r9);
                                    }
                                });
                                put("column", new ArrayList<Integer>() {
                                    {
                                        add(finalF_c4);
                                        add(finalF_c5);
                                    }
                                });
                            }
                        });

                    }
                }

                Map<String, Object> filterSelect = (Map<String, Object>) newFilterObj.get("filter_select");
                if (MapUtil.isNotEmpty(filterSelect) && MapUtil.isNotEmpty(filter)) {
                    Map<String, Object> newFilterObjFilters = new HashMap<>();
                    for (Map.Entry<String, Object> entrya : filter.entrySet()) {
                        Map<String, Object> f = (Map<String, Object>) entrya.getValue();
                        Integer f_cindex = (Integer) f.get("cindex");
                        if (f_cindex < st) {
                            Map<String, Object> newFilter = (Map<String, Object>) newFilterObj.get("filter");
                            Map<String, Object> newFilter2 = (Map<String, Object>) newFilterObj.get("filter");
                            if (CollectionUtil.isEmpty(newFilter)) {
                                newFilter = new HashMap<>();
                            }
                            newFilter2.putAll(f);
                            newFilter2.put("edc", f_c2);
                            newFilter.put((f_cindex - f_c1) + "", newFilter2);
                            newFilterObj.put("filter", newFilter);
                        } else if (f_cindex > ed) {
                            f_cindex -= slen;
                            Map<String, Object> newFilter = (Map<String, Object>) newFilterObj.get("filter");
                            if (CollectionUtil.isEmpty(newFilter)) {
                                newFilter = new HashMap<>();
                            }
                            newFilter.putAll(f);
                            newFilter.put("cindex", f_cindex);
                            newFilter.put("str", f_c1);
                            newFilter.put("edc", f_c2);
                            newFilterObjFilters.put((f_cindex - f_c1) + "", newFilter);

                        }
                    }
                    newFilterObj.put("filter", newFilterObjFilters);
                }
            }
        }

        if (MapUtil.isNotEmpty(newFilterObj) && MapUtil.isNotEmpty((Map<String, Object>) newFilterObj.get("filter"))) {
            Map<String, Object> rowhidden = (Map<String, Object>) config.get("rowhidden");
            if (MapUtil.isEmpty(rowhidden)) {
                rowhidden = new HashMap<>();
            }
            Map<String, Object> newFilterObjFilter = (Map<String, Object>) newFilterObj.get("filter");
            for (Map.Entry<String, Object> entrya : newFilterObjFilter.entrySet()) {
                String key = entrya.getKey();
                Map<String, Object> value = (Map<String, Object>) entrya.getValue();
                Map<String, Object> rowhidden2 = (Map<String, Object>) value.get("rowhidden");
                for (Map.Entry<String, Object> entryb : rowhidden2.entrySet()) {
                    rowhidden.put(entryb.getKey(), 0);
                }
            }
        }

        //条件格式
        List<Map<String, Object>> CFarr = (List<Map<String, Object>>) sheet.get("luckysheet_conditionformat_save");
        List<Map<String, Object>> newCFarr = new ArrayList<>();
        if (CFarr != null && !CFarr.isEmpty()) {
            for (int i = 0; i < CFarr.size(); i++) {
                Map<String, Object> cf = CFarr.get(i);
                List<Map<String, Object>> cf_range = (List<Map<String, Object>>) cf.get("cellrange");
                List<Map<String, Object>> cf_new_range = new ArrayList<>();

                for (int j = 0; j < cf_range.size(); j++) {
                    Map<String, Object> r = cf_range.get(j);
                    List<Integer> row = (List<Integer>) r.get("row");
                    List<Integer> column = (List<Integer>) r.get("column");
                    Integer CFr1 = row.get(0);
                    Integer CFr2 = row.get(1);
                    Integer CFc1 = column.get(0);
                    Integer CFc2 = column.get(1);
                    if (!(CFc1 >= st && CFc2 <= ed)) {
                        if (CFc1 > ed) {
                            CFc1 = CFc1 - slen;
                            CFc2 = CFc2 - slen;
                        } else if (CFc1 < st) {
                            if (CFc2 < st) {
                            } else if (CFc2 <= ed) {
                                CFc2 = st - 1;
                            } else {
                                CFc2 -= slen;
                            }
                        } else {
                            if (CFc2 > ed) {
                                CFc1 = st;
                                CFc2 -= slen;
                            }
                        }
                        Integer finalCFc = CFc1;
                        Integer finalCFc1 = CFc2;
                        cf_new_range.add(new HashMap<String, Object>() {
                            {
                                put("row", CollectionUtil.newArrayList(CFr1, CFr2));
                                put("column", CollectionUtil.newArrayList(finalCFc, finalCFc1));
                            }
                        });
                    }

                }
                if (!cf_new_range.isEmpty()) {
                    cf.put("cellrange", cf_new_range);
                    newCFarr.add(cf);
                }
            }

        }
        //交替颜色配置变动
        List<Map<String, Object>> AFarr = (List<Map<String, Object>>) sheet.get("luckysheet_alternateformat_save");
        List<Map<String, Object>> newAFarr = new ArrayList<>();
        if (CollectionUtil.isNotEmpty(AFarr)) {
            for (int i = 0; i < AFarr.size(); i++) {
                Map<String, Object> AF = AFarr.get(i);
                Map<String, Object> cellrange = (Map<String, Object>) AF.get("cellrange");
                List<Integer> row = (List<Integer>) cellrange.get("row");
                List<Integer> column = (List<Integer>) cellrange.get("column");
                Integer AFr1 = row.get(0);
                Integer AFr2 = row.get(1);
                Integer AFc1 = column.get(0);
                Integer AFc2 = column.get(1);
                if (StringUtils.equals(type, "row")) {
                    if (!(AFr1 >= st && AFr2 <= ed)) {
                        Map<String, Object> af = new HashMap<>();
                        af.putAll(AF);
                        if (AFr1 > ed) {
                            AFr1 -= slen;
                            AFr2 -= slen;
                        } else if (AFr1 < st) {
                            if (AFr2 < st) {
                            } else if (AFr2 <= ed) {
                                AFr2 = st - 1;
                            } else {
                                AFr2 -= slen;
                            }
                        } else {
                            if (AFr2 > ed) {
                                AFr1 = st;
                                AFr2 -= slen;
                            }
                        }
                        Integer finalAFr2 = AFr1;
                        Integer finalAFr3 = AFr2;
                        Integer finalAFc2 = AFc1;
                        Integer finalAFc3 = AFc2;
                        af.put("cellrange", new HashMap<String, Object>() {
                            {
                                put("row", CollectionUtil.newArrayList(finalAFr2, finalAFr3));
                                put("column", CollectionUtil.newArrayList(finalAFc2, finalAFc3));
                            }
                        });
                        newAFarr.add(af);
                    }
                } else if (StringUtils.equals(type, "col")) {
                    if (!(AFc1 >= st && AFc2 <= ed)) {
                        Map<String, Object> af = new HashMap<>();
                        af.putAll(AF);
                        if (AFc1 > ed) {
                            AFc1 -= slen;
                            AFc2 -= slen;
                        } else if (AFc1 < st) {
                            if (AFc2 < st) {
                            } else if (AFc2 <= ed) {
                                AFc2 = st - 1;
                            } else {
                                AFc2 -= slen;
                            }
                        } else {
                            if (AFc2 > ed) {
                                AFc1 = st;
                                AFc2 -= slen;
                            }
                        }

                        Integer finalAFr = AFr1;
                        Integer finalAFr1 = AFr2;
                        Integer finalAFc = AFc1;
                        Integer finalAFc1 = AFc2;
                        af.put("cellrange", new HashMap<String, Object>() {
                            {
                                put("row", CollectionUtil.newArrayList(finalAFr, finalAFr1));
                                put("column", CollectionUtil.newArrayList(finalAFc, finalAFc1));
                            }
                        });
                        newAFarr.add(af);
                    }
                }
            }
        }
        //数据验证配置变动
        Map<String, Object> dataVerification = (Map<String, Object>) sheet.get("dataVerification");
        if (MapUtil.isNotEmpty(dataVerification)) {
            for (Map.Entry<String, Object> entry : merge.entrySet()) {
                String key = entry.getKey();
                String[] keyArr = key.split("_");
                Integer r = Integer.parseInt(keyArr[0]);
                Integer c = Integer.parseInt(keyArr[1]);
                if (StringUtils.equals(type, "row")) {
                    if (r < st) {
                        newDataVerification.put(r + "_" + c, entry.getValue());
                    } else if (r > ed) {
                        newDataVerification.put((r - slen) + "_" + c, entry.getValue());
                    }
                } else if (StringUtils.equals(type, "col")) {
                    if (c < st) {
                        newDataVerification.put(r + "_" + c, entry.getValue());
                    } else if (c > ed) {
                        newDataVerification.put(r + "_" + (c - slen), entry.getValue());
                    }
                }
            }
        }
        //超链接配置变动
        if (MapUtil.isNotEmpty(hyperlink)) {
            for (Map.Entry<String, Object> entry : hyperlink.entrySet()) {
                String key = entry.getKey();
                Object item = entry.getValue();
                Integer r = Integer.parseInt(key.split("_")[0]);
                Integer c = Integer.parseInt(key.split("_")[1]);
                if (StringUtils.equals(type, "row")) {
                    if (r < st) {
                        newHyperlink.put(r + "_" + c, item);
                    } else if (r > ed) {
                        newHyperlink.put((r - slen) + "_" + c, item);
                    }
                } else if (StringUtils.equals(type, "col")) {
                    if (c < st) {
                        newHyperlink.put(r + "_" + c, item);
                    } else if (c > ed) {
                        newHyperlink.put(r + "_" + (c - slen), item);
                    }
                }
            }
        }
        //主逻辑
        String type1 = "";
        if (StringUtils.equals(type, "row")) {
            type1 = "r";
            //行高配置变动
            Map<String, Object> rowlen = (Map<String, Object>) config.get("rowlen");
            if (rowlen == null) {
                rowlen = new HashMap<>();
            }
            Map<String, Object> rowlen_new = new HashMap<>();
            for (Map.Entry<String, Object> entry : rowlen.entrySet()) {
                String key = entry.getKey();
                Integer r = Integer.parseInt(key);
                if (r < st) {
                    rowlen_new.put(r + "", rowlen.get(r + ""));
                } else if (r > ed) {
                    rowlen_new.put((r - slen) + "", rowlen.get(r + ""));
                }
            }
            config.put("rowhidden", rowlen_new);
            //边框配置变动
            List<Map<String, Object>> borderInfo = (List<Map<String, Object>>) config.get("borderInfo");
            if (CollectionUtil.isNotEmpty(borderInfo)) {
                List<Map<String, Object>> newBorderInfo = new ArrayList<>();
                for (Map<String, Object> border : borderInfo) {
                    String rangeType = (String) border.get("rangeType");
                    if (StringUtils.equals(rangeType, "range")) {
                        List<Map<String, Object>> borderRangeList = (List<Map<String, Object>>) border.get("range");
                        List<Map<String, Object>> emptyRange = new ArrayList<>();
                        for (int i = 0; i < borderRangeList.size(); i++) {
                            Map<String, Object> borderRange = borderRangeList.get(i);
                            List<Integer> row = (List<Integer>) borderRange.get("row");
                            Integer bd_r1 = row.get(0);
                            Integer bd_r2 = row.get(1);
                            for (int r = st; r <= ed; r++) {
                                if (r < bd_r1) {
                                    bd_r1 -= 1;
                                    bd_r2 -= 1;
                                } else if (r <= bd_r2) {
                                    bd_r2 -= 1;
                                }
                            }
                            if (bd_r2 >= bd_r1) {
                                Integer finalBd_r = bd_r1;
                                Integer finalBd_r1 = bd_r2;
                                emptyRange.add(new HashMap<String, Object>() {
                                    {
                                        put("row", CollectionUtil.newArrayList(finalBd_r, finalBd_r1));
                                        put("column", borderRange.get("column"));
                                    }
                                });
                            }
                        }
                        if (CollectionUtil.isNotEmpty(emptyRange)) {
                            newBorderInfo.add(new HashMap<String, Object>() {
                                {
                                    put("rangeType", "range");
                                    put("borderType", border.get("borderType"));
                                    put("style", border.get("style"));
                                    put("color", border.get("color"));
                                    put("range", emptyRange);
                                }
                            });

                        }
                    } else if (StringUtils.equals(rangeType, "cell")) {
                        Map<String, Object> value = (Map<String, Object>) border.get("value");
                        Integer row_index = (Integer) value.get("row_index");
                        if (row_index < st) {
                            newBorderInfo.add(border);
                        } else if (row_index > ed) {
                            value.put("row_index", row_index - (ed - st + 1));
                            newBorderInfo.add(border);
                        }
                    }
                }
                config.put("borderInfo", newBorderInfo);
            }
            //删除选中行
            List<List<Map<String, Object>>> data_new = new ArrayList<>();
            for (int i = 0; i < data.size(); i++) {
                if (i < st || i > slen) {
                    data_new.add(data.get(i));
                }
            }
            data = data_new;
        }
        if (StringUtils.equals(type, "col")) {
            type1 = "c";
            //列宽配置变动
            Map<String, Object> columnlen = (Map<String, Object>) config.get("columnlen");
            if (columnlen == null) {
                columnlen = new HashMap<>();
            }
            Map<String, Object> columnlen_new = new HashMap<>();
            for (Map.Entry<String, Object> entry : columnlen.entrySet()) {
                String key = entry.getKey();
                Integer c = Integer.parseInt(key);
                if (c < st) {
                    columnlen_new.put(c + "", columnlen.get(c + ""));
                } else if (c > ed) {
                    columnlen_new.put((c - slen) + "", columnlen.get(c + ""));
                }
            }
            config.put("columnlen", columnlen_new);
            //隐藏列配置变动
            Map<String, Object> colhidden = (Map<String, Object>) config.get("colhidden");
            if (CollectionUtil.isEmpty(colhidden)) {
                colhidden = new HashMap<>();
            }
            Map<String, Object> colhidden_new = new HashMap<>();
            for (Map.Entry<String, Object> entry : colhidden.entrySet()) {
                String key = entry.getKey();
                Integer c = Integer.parseInt(key);
                if (c < st) {
                    colhidden_new.put(c + "", colhidden.get(c + ""));
                } else if (c > ed) {
                    colhidden_new.put((c - slen) + "", colhidden.get(c + ""));
                }
            }
            config.put("colhidden", colhidden_new);
            //边框配置变动
            List<Map<String, Object>> borderInfo = (List<Map<String, Object>>) config.get("borderInfo");
            if (CollectionUtil.isNotEmpty(borderInfo)) {
                List<Map<String, Object>> newBorderInfo = new ArrayList<>();
                for (int i = 0; i < borderInfo.size(); i++) {
                    Map<String, Object> border = borderInfo.get(i);
                    String rangeType = (String) border.get("rangeType");
                    if (StringUtils.equals(rangeType, "range")) {
                        List<Map<String, Object>> borderRangeList = (List<Map<String, Object>>) border.get("range");
                        List<Map<String, Object>> emptyRange = new ArrayList<>();
                        for (int j = 0; j < borderRangeList.size(); j++) {
                            Map<String, Object> borderRange = borderRangeList.get(j);
                            List<Integer> column = (List<Integer>) borderRange.get("column");
                            Integer bd_c1 = column.get(0);
                            Integer bd_c2 = column.get(1);
                            for (int c = st; c <= ed; c++) {
                                if (c < bd_c1) {
                                    bd_c1 -= 1;
                                    bd_c2 -= 1;
                                } else if (c <= bd_c2) {
                                    bd_c2 -= 1;
                                }
                            }
                            if (bd_c2 >= bd_c1) {
                                Integer finalBd_c = bd_c1;
                                Integer finalBd_c1 = bd_c2;
                                emptyRange.add(new HashMap<String, Object>() {
                                    {
                                        put("row", borderRange.get("row"));
                                        put("column", CollectionUtil.newArrayList(finalBd_c, finalBd_c1));
                                    }
                                });
                            }
                        }
                        if (CollectionUtil.isNotEmpty(emptyRange)) {
                            newBorderInfo.add(new HashMap<String, Object>() {
                                {
                                    put("rangeType", "range");
                                    put("borderType", border.get("borderType"));
                                    put("style", border.get("style"));
                                    put("color", border.get("color"));
                                    put("range", emptyRange);
                                }
                            });
                        }
                    } else if (StringUtils.equals(rangeType, "cell")) {

                        Map<String, Object> value = (Map<String, Object>) border.get("value");
                        Integer col_index = (Integer) value.get("col_index");
                        if (col_index < st) {
                            newBorderInfo.add(border);
                        } else if (col_index > ed) {
                            value.put("col_index", col_index - (ed - st + 1));
                            newBorderInfo.add(border);
                        }
                    }
                }
                config.put("borderInfo", newBorderInfo);
            }

            for (int r = 0; r < data.size(); r++) {
                List<Map<String, Object>> row = data.get(r);
                //删除该行选中列
                List<Map<String, Object>> row_new = new ArrayList<>();
                for (int c = 0; c < row.size(); c++) {
                    if (c < st || c > slen) {
                        row_new.add(row.get(c));
                    }
                }
                data.set(r, row_new);
            }
        }

        // sheet.put("data", data);
        // sheet.put("config", config);
        // sheet.put("calcChain", newCalcChain);
        // sheet.put("filter", newFilterObj);
        // sheet.put("filter_select", newFilterObj.get("filter_select"));
        // sheet.put("luckysheet_conditionformat_save", newCFarr);
        // sheet.put("dataVerification", newDataVerification);
        // sheet.put("hyperlink", newHyperlink);
        return sheet;
    }

    //将 List<Integer> 拆分成多个连续的区间
    public static List<List<Integer>> splitIntoRanges(List<Integer> numbers) {
        List<List<Integer>> result = new ArrayList<>();
        if (numbers == null || numbers.isEmpty()) {
            return result;
        }

        List<Integer> currentRange = new ArrayList<>();
        currentRange.add(numbers.get(0));

        for (int i = 1; i < numbers.size(); i++) {
            if (numbers.get(i) == numbers.get(i - 1) + 1) {
                // Extend the current sequence
                currentRange.add(numbers.get(i));
            } else {
                // Save the current sequence and start a new one
                result.add(new ArrayList<>(currentRange));
                currentRange.clear();
                currentRange.add(numbers.get(i));
            }
        }

        // Save the last sequence
        result.add(new ArrayList<>(currentRange));

        return result;
    }

    private static List<List<Integer>> splitIntoContinuousIntervals(List<Integer> list) {
        List<List<Integer>> result = new ArrayList<>();
        if (list == null || list.isEmpty()) {
            return result;
        }
        Collections.sort(list);
        List<Integer> temp = new ArrayList<>();
        temp.add(list.get(0));
        for (int i = 1; i < list.size(); i++) {
            if (list.get(i) - list.get(i - 1) == 1) {
                temp.add(list.get(i));
            } else {
                result.add(temp);
                temp = new ArrayList<>();
                temp.add(list.get(i));
            }
        }
        result.add(temp);
        return result;
    }

    public static String getCellFormula(Integer r, Integer c, Integer i, List<List<Map<String, Object>>> data) {
        Map<String, Object> cell = new HashMap<>();
        if (!data.isEmpty()) {
            cell = data.get(r).get(c);
        }
        return (String) cell.get("f");

    }
}
